package cap.dao.impl;

import cap.bean.Article;
import cap.bean.User;
import cap.dao.ArticleDao;
import cap.dao.UserDao;
import cap.db.DBPool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ArticleDaoImpl implements ArticleDao {
	Connection conn=null;
	/**
	 *查询热门博主
	 */
	@Override
	public List<User> getActiveUser(int num) {//
		UserDao userDao = new UserDaoImpl();
		User u = null;
		List<User> uList = null;
		ResultSet rs = null;
		PreparedStatement ps=null;
		try {
			uList = userDao.getAllUser();
			if (num <= uList.size()) {
				uList = new ArrayList<User>();
				String sql="select user_id, COUNT(user_id) from article group by user_id order by COUNT(user_id) DESC limit 1,?";
				conn=DBPool.getConn();
				ps=conn.prepareStatement(sql);
				ps.setInt(1,num);
				rs=ps.executeQuery();
				while (rs.next()) {
					u = userDao.getUserById(rs.getInt("user_id"));
					uList.add(u);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}
		return uList;
	}
	/**
	 *根据用户id获取文章
	 */
	@Override
	public List<Article> getByUserId(int userId) {
		PreparedStatement ps=null;
		ResultSet rs = null;
		List<Article> artList = null;
		try {
			artList = new ArrayList<Article>();
			String sql="select * from article where user_id=? order by publish_time DESC";
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1,userId);
			rs=ps.executeQuery();
			while (rs.next()) {
				Article art = new Article();
				art.setId(rs.getInt("id"));
				art.setTitle(rs.getString("title"));
				art.setUserId(rs.getInt("user_id"));
				art.setSysCategoryId(rs.getInt("sys_category_id"));
				art.setCategoryId(rs.getInt("category_id"));
				art.setContent(rs.getString("content"));
				art.setSummary(rs.getString("summary"));
				art.setPublishTime(rs.getString("publish_time"));
				art.setIsTop(rs.getInt("is_top"));
				art.setIsDelete(rs.getInt("is_delete"));
				art.setCount(rs.getInt("count"));
				artList.add(art);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}
		return artList;
	}

	/**
	 *根据id查询文章
	 */
	@Override
	public Article getById(int id) {
		ResultSet rs = null;
		Article art = null;
		PreparedStatement ps=null;
		try {
			String sql="select * from article where id=?";
			art = new Article();
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1,id);
			rs=ps.executeQuery();
			while (rs.next()) {
				art.setId(rs.getInt("id"));
				art.setTitle(rs.getString("title"));
				art.setUserId(rs.getInt("user_id"));
				art.setSysCategoryId(rs.getInt("sys_category_id"));
				art.setCategoryId(rs.getInt("category_id"));
				art.setContent(rs.getString("content"));
				art.setSummary(rs.getString("summary"));
				art.setPublishTime(rs.getString("publish_time"));
				art.setIsTop(rs.getInt("is_top"));
				art.setIsDelete(rs.getInt("is_delete"));
				art.setCount(rs.getInt("count"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}
		return art;
	}

	/**
	 *添加文章
	 */
	@Override
	public int insertArticle(String title, int userId, int scgId, int cgId, String content, String summary) {
		int res = -1;
		PreparedStatement ps=null;
		try {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			Date date = new Date();
			String nowTime=sdf.format(date);
			String sql="insert into article (user_id,sys_category_id,category_id,title,content,summary,publish_time,is_top,is_delete) values(?, ?, ?, ?, ?, ?,?, 0, 0)";
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1,userId);
			ps.setInt(2,scgId);
			ps.setInt(3,cgId);
			ps.setString(4,title);
			ps.setString(5,content);
			ps.setString(6,summary);
			ps.setString(7,nowTime);
			res=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBPool.close(null,ps,conn);
		}
		return res;
	}

	/**
	 *修改文章
	 */
	@Override
	public int updateArticle(int artId, String title, int userId, int scgId, int cgId, String content, String summary) {
		PreparedStatement ps=null;
		ResultSet rs=null;
		int res = -1;
		try {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			Date date = new Date();
			String nowTime=sdf.format(date);
			String sql="update article " +
					"set title=?, user_id=?, sys_category_id=?, category_id=?, content=?, summary=?, publish_time=?, is_top=0, is_delete=0" +
					" where id=?";
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setString(1,title);
			ps.setInt(2,userId);
			ps.setInt(3,scgId);
			ps.setInt(4,cgId);
			ps.setString(5,content);
			ps.setString(6,summary);
			ps.setString(7,nowTime);
			res=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}
		return res;
	}

	/**
	 *根据id删除文章
	 */
	@Override
	public int deleteArtical(int artId) {
		PreparedStatement ps=null;
		ResultSet rs=null;
		int res = -1;
		try {
			String sql="update article set is_delete=1 where id=?";
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1,artId);
			res=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}
		return res;
	}

	/**
	 *获取所有文章
	 */
	@Override
	public List<Article> getAllArtical() {
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<Article> artList = null;
		try {
			String sql="select * from article where is_delete=0 order by publish_time DESC";
			artList = new ArrayList<Article>();
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while (rs.next()) {
				Article art = new Article();
				art.setId(rs.getInt("id"));
				art.setTitle(rs.getString("title"));
				art.setUserId(rs.getInt("user_id"));
				art.setSysCategoryId(rs.getInt("sys_category_id"));
				art.setCategoryId(rs.getInt("category_id"));
				art.setContent(rs.getString("content"));
				art.setSummary(rs.getString("summary"));
				art.setPublishTime(rs.getString("publish_time"));
				art.setIsTop(rs.getInt("is_top"));
				art.setIsDelete(rs.getInt("is_delete"));
				artList.add(art);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}
		return artList;
	}

	/**
	 *模糊查询文章
	 */
	@Override
	public List<Article> search(String q) {
		List<Article> artList = null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			String sql="select * from article where title like ? or content like ? or summary like ? order by publish_time DESC";
			artList = new ArrayList<Article>();
			q = '%' + q + '%';
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setString(1,q);
			ps.setString(2,q);
			ps.setString(3,q);
			rs=ps.executeQuery();
			while (rs.next()) {
				Article art = new Article();
				art.setId(rs.getInt("id"));
				art.setTitle(rs.getString("title"));
				art.setUserId(rs.getInt("user_id"));
				art.setSysCategoryId(rs.getInt("sys_category_id"));
				art.setCategoryId(rs.getInt("category_id"));
				art.setContent(rs.getString("content"));
				art.setSummary(rs.getString("summary"));
				art.setPublishTime(rs.getString("publish_time"));
				art.setIsTop(rs.getInt("is_top"));
				art.setIsDelete(rs.getInt("is_delete"));
				artList.add(art);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}
		return artList;
	}
	/**
	 *分页展示文章
	 */
	@Override
	public List<Article> getArticleByPage(int curPage, int size) {
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<Article> artList = null;
		try {
			String sql="select a.*,u.username from article a, muser u  where u.id=a.user_id and a. is_delete=0 order by publish_time DESC limit ?,?";
			artList = new ArrayList<Article>();
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1,curPage);
			ps.setInt(2,size);
			rs=ps.executeQuery();
			while (rs.next()) {
				Article art = new Article();
				art.setId(rs.getInt("id"));
				art.setTitle(rs.getString("title"));
				art.setUserId(rs.getInt("user_id"));
				art.setSysCategoryId(rs.getInt("sys_category_id"));
				art.setCategoryId(rs.getInt("category_id"));
				art.setContent(rs.getString("content"));
				art.setSummary(rs.getString("summary"));
				art.setPublishTime(rs.getString("publish_time"));
				art.setIsTop(rs.getInt("is_top"));
				art.setIsDelete(rs.getInt("is_delete"));
				art.setCount(rs.getInt("count"));
				try {
					art.setUsername(rs.getString("username"));
				} catch (Exception e) {
					art.setUsername("");
				}
				artList.add(art);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}
		return artList;
	}
	/**
	 *根据系统类别id分页查询显示文章
	 */
	@Override
	public List<Article> getFilterArticleByPage(int curPage, int size,int sysCategoryId) {
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<Article> artList = null;
		try {
			String sql="select a.*,u.username from article a, muser u  where u.id=a.user_id and a.sys_category_id = ? and a. is_delete=0 order by publish_time DESC limit ?,?";
			artList = new ArrayList<Article>();
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1,sysCategoryId);
			ps.setInt(2,curPage);
			ps.setInt(3,size);
			rs=ps.executeQuery();
			while (rs.next()) {
				Article art = new Article();
				art.setId(rs.getInt("id"));
				art.setTitle(rs.getString("title"));
				art.setUserId(rs.getInt("user_id"));
				art.setSysCategoryId(rs.getInt("sys_category_id"));
				art.setCategoryId(rs.getInt("category_id"));
				art.setContent(rs.getString("content"));
				art.setSummary(rs.getString("summary"));
				art.setPublishTime(rs.getString("publish_time"));
				art.setIsTop(rs.getInt("is_top"));
				art.setIsDelete(rs.getInt("is_delete"));
				art.setCount(rs.getInt("count"));
				try {
					art.setUsername(rs.getString("username"));
				} catch (Exception e) {
					art.setUsername("");
				}
				artList.add(art);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}
		return artList;
	}
	/**
	 *分页查询所有用户文章
	 */
	@Override
	public List<Article> getArticleByPageUserId(int curPage, int size, int userId) {
		ResultSet rs = null;
		PreparedStatement ps=null;
		List<Article> artList=null;
		try {
			String sql="SELECT article.*,c.category_name as cname,sc.category_name AS scgname,u.username "
					+ "FROM article "
					+ " INNER JOIN category c ON article.category_id = c.id"
					+ " INNER JOIN sys_category sc ON article.sys_category_id = sc.id"
					+ " INNER JOIN muser u ON article.user_id = u.id AND c.user_id = u.id "
					+" where article.is_delete=0 and article.user_id=? order by publish_time DESC limit ?,?";
			artList = new ArrayList<Article>();
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1,userId);
			ps.setInt(2,curPage);
			ps.setInt(3,size);
			rs=ps.executeQuery();
			while (rs.next()) {
				Article art = new Article();
				art.setId(rs.getInt("id"));
				art.setTitle(rs.getString("title"));
				art.setUserId(rs.getInt("user_id"));
				art.setSysCategoryId(rs.getInt("sys_category_id"));
				art.setCategoryId(rs.getInt("category_id"));
				art.setContent(rs.getString("content"));
				art.setSummary(rs.getString("summary"));
				art.setPublishTime(rs.getString("publish_time"));
				art.setIsTop(rs.getInt("is_top"));
				art.setIsDelete(rs.getInt("is_delete"));
				art.setCount(rs.getInt("count"));
				try {
					art.setUsername(rs.getString("username"));
					art.setCategoryName(rs.getString("cname"));
					art.setScName(rs.getString("scgname"));
				} catch (Exception e) {
					art.setUsername("");
					art.setCategoryName("");
					art.setScName("");
				}

				artList.add(art);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}
		return artList;
	}
	/**
	 *根据类别id和用户id查询文章
	 */
	@Override
	public List<Article> getArticleByPageUserCategoryId(int curPage, int size, int userId,int categoryId) {
		List<Article> artList = null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			String sql="SELECT article.*,c.category_name as cname,sc.category_name AS scgname,u.username "
					+ "FROM article "
					+ " INNER JOIN category c ON article.category_id = c.id"
					+ " INNER JOIN sys_category sc ON article.sys_category_id = sc.id"
					+ " INNER JOIN muser u ON article.user_id = u.id AND c.user_id = u.id "
					+" where article.is_delete=0 and article.user_id=? and article.category_id=? order by publish_time DESC) limit ?,?";
			artList = new ArrayList<Article>();
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1,userId);
			ps.setInt(2,categoryId);
			ps.setInt(3,curPage);
			ps.setInt(4,size);
			rs=ps.executeQuery();
			while (rs.next()) {
				Article art = new Article();
				art.setId(rs.getInt("id"));
				art.setTitle(rs.getString("title"));
				art.setUserId(rs.getInt("user_id"));
				art.setSysCategoryId(rs.getInt("sys_category_id"));
				art.setCategoryId(rs.getInt("category_id"));
				art.setContent(rs.getString("content"));
				art.setSummary(rs.getString("summary"));
				art.setPublishTime(rs.getString("publish_time"));
				art.setIsTop(rs.getInt("is_top"));
				art.setIsDelete(rs.getInt("is_delete"));
				art.setCount(rs.getInt("count"));
				try {
					art.setUsername(rs.getString("username"));
					art.setCategoryName(rs.getString("cname"));
					art.setScName(rs.getString("scgname"));
				} catch (Exception e) {
					art.setUsername("");
					art.setCategoryName("");
					art.setScName("");
				}

				artList.add(art);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}
		return artList;
	}

	/**
	 *根据类别id,系统类别id,文章id查询文章
	 */
	@Override
	public List<Article> getBycgIdorscgId(int cgId, int scgId, int artId) {
		List<Article> artList = null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			String sql="select * from article where is_delete=0 and id<>? and category_id=? and sys_category_id=? order by publish_time limit 1,5";
			artList = new ArrayList<Article>();
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1,artId);
			ps.setInt(2,cgId);
			ps.setInt(3,scgId);
			rs=ps.executeQuery();
			while (rs.next()) {
				Article art = new Article();
				art.setId(rs.getInt("id"));
				art.setTitle(rs.getString("title"));
				art.setUserId(rs.getInt("user_id"));
				art.setSysCategoryId(rs.getInt("sys_category_id"));
				art.setCategoryId(rs.getInt("category_id"));
				art.setContent(rs.getString("content"));
				art.setSummary(rs.getString("summary"));
				art.setPublishTime(rs.getString("publish_time"));
				art.setIsTop(rs.getInt("is_top"));
				art.setIsDelete(rs.getInt("is_delete"));
				art.setCount(rs.getInt("count"));
				artList.add(art);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBPool.close(rs,ps,conn);
		}
		return artList;
	}
	/**
	 *查询置顶前十篇文章
	 */
	@Override
	public List<Article> topTenArticle(){
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<Article> artList = null;
		try {
			artList = new ArrayList<Article>();
			String sql="select * from article order by count desc limit 1,10";
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				Article art = new Article();
				art.setId(rs.getInt("id"));
				art.setTitle(rs.getString("title"));
				art.setUserId(rs.getInt("user_id"));
				art.setSysCategoryId(rs.getInt("sys_category_id"));
				art.setCategoryId(rs.getInt("category_id"));
				art.setContent(rs.getString("content"));
				art.setSummary(rs.getString("summary"));
				art.setPublishTime(rs.getString("publish_time"));
				art.setIsTop(rs.getInt("is_top"));
				art.setIsDelete(rs.getInt("is_delete"));
				art.setCount(rs.getInt("count"));
				artList.add(art);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBPool.close(rs,ps,conn);
		}
		return artList;
	}

	/**
	 *根据id修改文章点击数
	 */
	@Override
	public int updateCount(int artId) {
		PreparedStatement ps=null;
		ResultSet rs=null;
		int res=0;
		try {
			String sql="update article set count=count+1 where id=?";
			conn=DBPool.getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1,artId);
			res=ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBPool.close(rs,ps,conn);
		}
		return res;
	}
}
